"""
Case Type   : 功能测试
Case Name   : 开启备机写，备机上创建临时表，unlogged表，外表物化视图
Create At   : 2023-12-11
Owner       : kyeleze
Description :
    1.开启备机写，并重启集群
    2.备机上创建临时表
    3.备节点上创建unlogged表
    4.备节点上创建外表
    5.备节点上创建物化视图
Expect      :
    1.成功
    2.成功
    3.成功
    4.成功
    5.成功
    6.成功
History     :
"""
import os
import unittest
from yat.test import Node
from yat.test import macro
from testcase.utils.Logger import Logger
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Common import Common

class PageTable(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f"----{os.path.basename(__file__)} start----")
        self.primary_sh = CommonSH('PrimaryDbUser')
        self.primary_node = Node('PrimaryDbUser')
        self.standby_sh = CommonSH('Standby1DbUser')
        self.common = Common()
        self.constant = Constant()
        self.table_name = 't_page_table_test062'
        self.materialized_view_name = 'mv_page_table_test062'
        self.incremental_view_name = 'iv_page_table_test062'
        self.wrapper_name = 'fdw_page_table_test062'
        self.server_name = 'ser_page_table_test062'
        self.foreign_table_name = 'ft_page_table_test062'
        self.temp_table_name = 'temp_page_table_test062'
        self.unlogged_table_name = 't_page_table_test062'
        self.defalt_support_extended_features = \
            self.common.show_param('support_extended_features')
        self.remote_execute_default = \
            self.common.show_param("enable_remote_execute")

    def test_page_table(self):
        step = "----step1:开启备机写，并重启集群 expect：成功----"
        self.log.info(step)
        self.log.info('----设置support_extended_features= on----')
        guc_res1 = self.primary_sh.execute_gsguc(
            'set',
            self.constant.GSGUC_SUCCESS_MSG,
            f'support_extended_features= on')
        self.log.info(guc_res1)
        self.assertTrue(guc_res1, '执行失败:' + step)
        guc_res2 = self.primary_sh.execute_gsguc(
            'set',
            self.constant.GSGUC_SUCCESS_MSG,
            f'enable_remote_execute= on')
        self.log.info(guc_res2)
        self.assertTrue(guc_res2, '执行失败:' + step)
        self.log.info('----重启数据库----')
        restart_cmd = f'source {macro.DB_ENV_PATH};' \
                      f'cm_ctl stop && cm_ctl start'
        restart_res = self.common.get_sh_result(self.primary_node, restart_cmd)
        self.log.info(restart_res)
        self.assertIn(self.constant.cm_stop_success_msg, restart_res,
                      '执行失败:' + step)
        self.assertIn(self.constant.cm_start_success_msg, restart_res,
                      '执行失败:' + step)

        step = '-----step2:备机上创建临时表 expect：成功-----'
        self.log.info(step)
        sql_cmd = f"drop table if exists {self.temp_table_name};" \
                  f"create global temporary table {self.temp_table_name} " \
                  f"(c_id int, c_real real, c_char char(50) default null, " \
                  f"c_clob clob, c_raw raw(20), c_blob blob, c_date date )" \
                  f"on commit preserve rows;" \
                  f"drop table {self.temp_table_name};"
        self.log.info(sql_cmd)
        sql_res = self.standby_sh.execut_db_sql(sql_cmd)
        self.log.info(sql_res)
        self.assertEqual(2, sql_res.count(self.constant.DROP_TABLE_SUCCESS),
                         '执行失败:' + step)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS, sql_res,
                      '执行失败:' + step)

        step = '-----step3:备节点上创建unlogged表 expect：成功-----'
        self.log.info(step)
        sql_cmd = f"drop table if exists {self.unlogged_table_name};" \
                  f"create unlogged table {self.unlogged_table_name} " \
                  f"(t1 int, t2 text);" \
                  f"drop table {self.unlogged_table_name};"
        self.log.info(sql_cmd)
        sql_res = self.standby_sh.execut_db_sql(sql_cmd)
        self.log.info(sql_res)
        self.assertEqual(2, sql_res.count(self.constant.DROP_TABLE_SUCCESS),
                         '执行失败:' + step)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS, sql_res,
                      '执行失败:' + step)

        step = '-----step4:备节点上创建外表 expect：成功-----'
        self.log.info(step)
        sql_cmd = f"drop foreign data wrapper if exists {self.wrapper_name};" \
                  f"create foreign data wrapper {self.wrapper_name};" \
                  f"drop server if exists {self.server_name};" \
                  f"create server {self.server_name} " \
                  f"foreign data wrapper {self.wrapper_name};" \
                  f"drop foreign table if exists {self.foreign_table_name};" \
                  f"create foreign table {self.foreign_table_name} " \
                  f"(a int, b int, c text) server {self.server_name};" \
                  f"drop foreign table {self.foreign_table_name};" \
                  f"drop server {self.server_name};" \
                  f"drop foreign data wrapper {self.wrapper_name};"
        self.log.info(sql_cmd)
        sql_res = self.standby_sh.execut_db_sql(sql_cmd)
        self.log.info(sql_res)
        self.assertEqual(
            2, sql_res.count(self.constant.DROP_FOREIGN_SUCCESS_MSG),
            '执行失败:' + step)

        step = '-----step5:备节点上创建物化视图 expect：成功-----'
        self.log.info(step)
        sql_cmd = f"drop table if exists {self.table_name};" \
                  f"create table {self.table_name}(t1 int, t2 text);" \
                  f"insert into {self.table_name} values (1, '第一行');" \
                  f"create materialized view {self.materialized_view_name} " \
                  f"as select * from {self.table_name};" \
                  f"create incremental materialized view " \
                  f"{self.incremental_view_name} as select * from " \
                  f"{self.table_name};" \
                  f"drop materialized view {self.materialized_view_name};" \
                  f"drop materialized view {self.incremental_view_name};" \
                  f"drop table {self.table_name};"
        self.log.info(sql_cmd)
        sql_res = self.standby_sh.execut_db_sql(sql_cmd)
        self.log.info(sql_res)
        self.assertEqual(2, sql_res.count("CREATE MATERIALIZED VIEW"),
                         '执行失败:' + step)
        self.assertEqual(2, sql_res.count("DROP MATERIALIZED VIEW"),
                         '执行失败:' + step)

    def tearDown(self):
        step = '----清理环境----'
        self.log.info(step)
        self.log.info('----恢复参数----')
        guc_res1 = self.primary_sh.execute_gsguc(
            'set',
            self.constant.GSGUC_SUCCESS_MSG,
            f'support_extended_features='
            f'{self.defalt_support_extended_features}')
        self.log.info(guc_res1)
        guc_res2 = self.primary_sh.execute_gsguc(
            'set',
            self.constant.GSGUC_SUCCESS_MSG,
            f'enable_remote_execute={self.defalt_support_extended_features}')
        self.log.info(guc_res2)
        self.log.info('----重启数据库----')
        restart_cmd = f'source {macro.DB_ENV_PATH};' \
                      f'cm_ctl stop && cm_ctl start'
        restart_res = self.common.get_sh_result(self.primary_node, restart_cmd)
        self.log.info(restart_res)
        self.assertTrue(guc_res1, '执行失败:' + step)
        self.assertTrue(guc_res2, '执行失败:' + step)
        self.assertIn(self.constant.cm_stop_success_msg, restart_res,
                      '执行失败:' + step)
        self.assertIn(self.constant.cm_start_success_msg, restart_res,
                      '执行失败:' + step)
        self.log.info(f"----{os.path.basename(__file__)} end----")

